Search SQL:用于全文检索的SQL扩展（二）

上篇文章（Search SQL:用于全文检索的SQL扩展（一））介绍了基于搜索的扩展语法，给出了用SQL实现全文检索的语法与示例。本章将根据前文所介绍的Search SQL检索语义相关的语法，以中文检索场景为例，说明对于一组特定的需求如何灵活运用Search SQL的语义实现全文检索功能。

创建含中文分词列的内表

Transwarp Search的用户Max收集了一组有关星环科技近期的新闻报道以及相关网页内容标题，现在需要根据一些不同的需求来分析这些内容。由于报道的内容主要为中文长文本数据，所以要通过全文检索来对其内容进行分析。

为了应对上述需求同时满足限制，Max决定创建一个用中文分词字段来存储检索内容的内表，从而既可实现传统的模糊查询（where like），又可以使用Search SQL中独有的语义检索功能。

创建内表并对中文列指定分词器

内表的建表语句如下所示：

create table news_analyze_zh(

   key1 string,

   content string append analyzer 'ZH' 'ik'  

)stored as ES

with shard number 10

replication 1;

• append ：表示该列既支持标准SQL的模糊查询，也支持语义检索。
  

• ik ：中文分词器之一。

向内表插入中文文本

建表成功后，用户Max需将收集到的文本数据插入 news_analyze_zh 表中。

实现文本插入的语句如下：

insert into news_analyze_zh(key1, content)values

('1', '全国人大财经委莅临星环科技');

insert into news_analyze_zh(key1, content)values

('2', '星环信息科技（上海）有限公司');

insert into news_analyze_zh(key1, content)values

('3', '星环科技荣获2017电信大数据司马奖“优秀成果”奖');

insert into news_analyze_zh(key1, content)values

('4', '星环前沿科技论坛九城巡展顺利落下帷幕');

insert into news_analyze_zh(key1, content)values

('5', '腾讯领投星环科技2.35亿元');

insert into news_analyze_zh(key1, content)values

('6', '明星环卫工的坚守');

insert into news_analyze_zh(key1, content)values

('7', '星环科技荣列大数据企业50强');

用CONTAINS查询进行分析

Max想要从这些新闻和网页标题找到和“星环”公司相关的内容。由于报道内容为非结构化数据，所以需要通过模糊查询实现。他首先尝试使用传统SQL中的like查询，执行下述语句。

like查询‘%星环%’

select * from news_analyze_zh where content like '%星环%';

查询结果中包含了所有含“星环”二字的文本。然而，key1=6 的这条数据中的“星环”二字，是从“明星环卫工”而来，不符合搜索和星环公司相关消息的目的，与实际需求有偏差。

这种情况恰好暴露了like查询只会按照单字匹配而没有考虑语义的缺点，为了实现更加精准的语义匹配，下面Max用Search SQL的CONSTAINTS语法再次进行搜索。

通过CONTAINS查询"星环"

select * from news_analyze_zh where contains(content, '星环');

从上面的结果可以发现该语句只返回了真正将“星环”作为一个独立单词出现的内容，排除了语义有偏差的 key1=6这条数据。表明CONTAINS查询攻克了like操作的缺陷，展现了其从语义出发的优点。

另外，通过对比两次查询的执行时间，Max发现CONTAINS语法的查询速度比like更快，性能更加优异。

于是，Max认为CONTAINS语义检索更适合非结构化的文本数据的分析。在接下来的检索需求中，Max尝试并探索CONTAINS的更多用法。

用NEAR操作符进行分析

Max想要了解大数据在电信行业中的发展情况，因此需要查找收集到的文本中是否有相关内容。

为了满足这一分析需求，Max认为在长篇的新闻报道中，“电信”和“数据”出现在同一句话中时，其相关性是远远高于两者分别出现在篇首和篇尾的。因此，若需要获取两者有联系的内容，应查询“电信”和“数据”这两个词出现间隔较小的文本。

根据上述分析，用户Max决定使用CONTAINS语法中的NEAR操作符，该操作符可返回包含在指定间隔内的多个分词后的单词的文本数据。

NEAR操作符查询“电信”和“数据”

select * from news_analyze_zh where contains(content, 'near((电信,数据),1,false)');

通过该语句的查询，系统返回了“电信”和“数据”间隔不超过1的新文本数据，符合Max的检索需求。

从示例可见，在CONTAINS查询语句中使用NEAR 操作符可以查询指定间隔内的多个分词后的单词，多用于查询有联系的多个单词。

用FUZZY操作符进行分析

除了上述两个查询需求，Max还希望获得所有与星环科技相关的内容，所查询的文本除了包含完整短语“星环科技”外，还应该返回包含“星环...科技”和“科技...星环”的文本内容，且“星环”和“科技”的距离不应该过长。

为了满足这一检索需求，Max认为，基础的CONTAINS语义检索只能返回包含“星环科技”的新闻数据，不能控制间隔；而NEAR操作符只能查询分词后的多个token，无法直接查询短语。因此，这两种查询都不是十分合适。

于是，Max决定使用CONTAINS语法中的FUZZY操作符，该操作符既可以查询到包含完整短语“星环科技”的数据，还可以查询短语“星环科技”分词后的多个token的位置进行有限变动的数据。具体查询语句如下：

FUZZY操作符模糊查询“星环科技”短语

select * from news_analyze_zh where contains(content, 'fuzzy(星环科技,2)');

该语句的结果不仅返回了包含完整短语“星环科技”的文本，还返回了包含“星环...科技”的内容。

通过Search SQL中的CONTAINS语法，以及CONSTAINS语法下的两种高级操作NEAR和FUZZY，Max快速高效的实现了各种查询需求。

总结

我们对于Search SQL通过上下两篇文章介绍了它的各种用法以及优势。

Search SQL为用户极大的简化了全文检索的实现过程，可以完全兼容所有的SQL语法（包括PL/SQL）。同时开发了丰富的检索语义，得以满足更多的检索需求。相比较于采用API的实现方式，Search SQL为实现全文检索提供了较低的门槛。

此外，Search SQL还对检索的性能进行了优化，大部分情况下，使用Search SQL可以获得比使用API有更高的执行速度。

Search SQL的应用还可以帮助减少业务逻辑的迁移成本，当从Oracle或DB2迁移至Transwarp时，用户只需要修改少量的SQL就可以实现。

Search SQL实现了超越SQL传统的like模糊查询功能，在易用性、性能、迁移成本三方面体现出了巨大的优势，可有效地帮助企业构建搜索引擎，为SQL服务于全文检索功能的实现提供支持。

---

往期原创文章

大数据开放实验室由星环信息科技（上海）有限公司运营，专门致力于大数据技术的研究和传播。若转载请在文章开头明显注明“文章来源于微信订阅号——大数据开放实验室”，并保留作者和账号介绍。